package dao;

import anno.Component;
import com.fasterxml.jackson.databind.ObjectMapper;
import jdk.internal.org.objectweb.asm.TypeReference;
import pojo.Attendance;
import pojo.AttendanceRecord;
import pojo.Course;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import static utils.JDBCUtils.executePageQuery;

@Component
public class AttendanceDAO extends AbstractBaseDao<Attendance> {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 插入新的考勤记录，并批量插入考勤记录到 `tb_attendance_record` 表
     *
     * @param attendance 考勤记录对象
     * @return 插入的考勤记录ID
     * @throws SQLException 数据库异常
     */
    public Integer insertAttendance(Attendance attendance) throws SQLException {
        // 插入考勤记录到 `tb_attendance` 表
        Integer attendanceId = insert(attendance);

        // 获取课程的学生信息
        Course course = findCourseById(attendance.getCourseId());
        List<Map<String, Object>> studentIds = parseStudentIds(course.getStudentIds());
        List<AttendanceRecord> attendanceRecords = new ArrayList<>();

        // 解析学生信息并创建考勤记录
        for (Map<String, Object> student : studentIds) {
            AttendanceRecord record = new AttendanceRecord();
            record.setAttendanceId(attendanceId);
            record.setStudentId((Integer) student.get("id"));
            record.setRecordType("check-in");
            record.setStatus("pending");
            record.setCreatedAt(attendance.getCreatedAt());
            attendanceRecords.add(record);
        }

        // 批量插入考勤记录到 `tb_attendance_record` 表
        batchInsertAttendanceRecords(attendanceRecords);

        return attendanceId;
    }

    /**
     * 根据课程ID查询课程信息
     *
     * @param courseId 课程ID
     * @return 课程对象
     * @throws SQLException 数据库异常
     */
    private Course findCourseById(Integer courseId) throws SQLException {
        String sql = "SELECT * FROM tb_courses WHERE id = ?";
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, courseId);
        if (rs.next()) {
            Course course = new Course();
            course.setId(rs.getInt("id"));
            course.setCourseName(rs.getString("course_name"));
            course.setTeacherId(rs.getInt("teacher_id"));
            course.setStartTime(rs.getTimestamp("start_time").toLocalDateTime());
            course.setEndTime(rs.getTimestamp("end_time").toLocalDateTime());
            course.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
            course.setStudentIds(rs.getString("student_ids"));
            course.setCourseImage(rs.getString("course_image"));
            return course;
        }
        return null;
    }

    /**
     * 解析学生信息
     *
     * @param studentIdsJson 学生信息JSON字符串
     * @return 学生信息列表
     */
    private List<Map<String, Object>> parseStudentIds(String studentIdsJson) {
        try {
            return objectMapper.readValue(studentIdsJson, List.class);
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        }
    }
    /**
     * 根据考勤ID查询该出勤记录表中，status比例，以及分别是谁
     *
     * @param attendanceId 考勤ID
     * @return 包含status比例和学生信息的Map
     * @throws SQLException 数据库异常
     */
    public Map<String, Object> getAttendanceStatusRatio(Integer attendanceId) throws SQLException {
        String sql = "SELECT ar.id, ar.status, u.id AS student_id, u.fullname AS student_name " +
                "FROM tb_attendance_record ar " +
                "JOIN tb_users u ON ar.student_id = u.id " +
                "WHERE ar.attendance_id = ?";
        ResultSet rs = (ResultSet) JDBCUtils.execute(sql, attendanceId);

        Map<String, Object> result = new HashMap<>();
        Map<String, Integer> statusCountMap = new HashMap<>();
        Map<String, List<Map<String, Object>>> statusStudentsMap = new HashMap<>();
        int totalCount = 0;

        // 初始化所有可能的status
        List<String> possibleStatuses = Arrays.asList("未签到", "已签到", "签到过期", "请假", "其他");
        for (String status : possibleStatuses) {
            statusCountMap.put(status, 0);
            statusStudentsMap.put(status, new ArrayList<>());
        }

        while (rs.next()) {
            int id = rs.getInt("id");
            String status = rs.getString("status");
            int studentId = rs.getInt("student_id");
            String studentName = rs.getString("student_name");

            // 将null值归类为“未签到”
            if (status == null) {
                status = "未签到";
            }

            // 更新status计数
            statusCountMap.put(status, statusCountMap.getOrDefault(status, 0) + 1);
            totalCount++;

            // 更新status对应的学生信息
            List<Map<String, Object>> students = statusStudentsMap.getOrDefault(status, new ArrayList<>());
            Map<String, Object> studentInfo = new HashMap<>();
            studentInfo.put("id", id); // 使用id
            studentInfo.put("student_id", studentId);
            studentInfo.put("student_name", studentName);
            students.add(studentInfo);
            statusStudentsMap.put(status, students);
        }

        result.put("statusCountMap", statusCountMap);
        result.put("totalCount", totalCount);
        result.put("statusStudentsMap", statusStudentsMap);

        return result;
    }

    /**
     * 批量插入考勤记录到 `tb_attendance_record` 表
     *
     * @param attendanceRecords 考勤记录列表
     * @throws SQLException 数据库异常
     */
    private void batchInsertAttendanceRecords(List<AttendanceRecord> attendanceRecords) throws SQLException {
        String sql = "INSERT INTO tb_attendance_record (attendance_id, student_id) VALUES (?, ?)";
        Connection conn = JDBCUtils.getConnection();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            for (AttendanceRecord record : attendanceRecords) {
                stmt.setInt(1, record.getAttendanceId());
                stmt.setInt(2, record.getStudentId());
                stmt.addBatch();
            }
            stmt.executeBatch();
        }
    }

    /**
     * 分页模糊查询考勤记录，管理员可以查询所有考勤记录
     *
     * @param teacherId 教师ID，如果为null，则查询所有教师的考勤记录
     * @param keyword   关键词，用于模糊查询
     * @param pageNum   页码
     * @param pageSize  每页记录数
     * @return 分页查询结果
     * @throws SQLException 数据库异常
     */
    public JDBCUtils.Page<Attendance> findAttendancesByPageAndName(Integer teacherId, String keyword, int pageNum, int pageSize) throws SQLException {
        String sql;
        Object[] params;
        if (teacherId != null) {
            sql = "SELECT a.*, c.course_name, c.course_image, u.fullname AS teacher_name " +
                    "FROM tb_attendance a " +
                    "JOIN tb_courses c ON a.course_id = c.id " +
                    "JOIN tb_users u ON a.teacher_id = u.id " +
                    "WHERE a.teacher_id = ? AND c.course_name LIKE ?";
            params = new Object[]{teacherId, "%" + keyword + "%"};
        } else {
            sql = "SELECT a.*, c.course_name, c.course_image, u.fullname AS teacher_name " +
                    "FROM tb_attendance a " +
                    "JOIN tb_courses c ON a.course_id = c.id " +
                    "JOIN tb_users u ON a.teacher_id = u.id " +
                    "WHERE c.course_name LIKE ?";
            params = new Object[]{"%" + keyword + "%"};
        }
        return executePageQuery(sql, new JDBCUtils.Page<>(pageNum, pageSize), this::mapResultSetToEntity, params);
    }


    /**
     * 将ResultSet映射为Attendance对象
     *
     * @param rs ResultSet对象
     * @return Attendance对象
     * @throws SQLException 数据库异常
     */
    @Override
    protected Attendance mapResultSetToEntity(ResultSet rs) throws SQLException {
        Attendance attendance = super.mapResultSetToEntity(rs);
        attendance.setCourseName(rs.getString("course_name"));
        attendance.setCourseImage(rs.getString("course_image"));
        attendance.setTeacherName(rs.getString("teacher_name"));
        return attendance;
    }
}